//	Company:	HTHK
//	NAME:	    GSCSearch.java
//				Group  Summary  Internal  Chargeable
//	TITLE:	    The maintence of Sum Group in Time Sheet System
//	FUNCTION:	The flow control interface of class Sum Group associate with the table
//	BY:	        Mike Cheng
//	DATE:	    11/2/2004
// --------------------------------------------------------------
// MODIFICATION HISTORY
// --------------------------------------------------------------
//	CR Log #:  99999  BY:  XXXX   DATE:  dd/mm/yyyy
//	ERROR:	XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
//	FIX:	XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
// --------------------------------------------------------------
package tss.projectreport;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;

import tss.common.DBServlet2;
import tss.rate.RateBean;
import tss.rate.RateProcess;

import com.ffpc.sql.DataException;
import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

public class GSISearch extends DBServlet2
{
	private Logger logger = Logger.getLogger(GSISearch.class);
	@Override
	protected String perform(HttpServletRequest request,
                             HttpServletResponse response,
                             StrutsMessage message)
    throws  ServletException,
            IOException,
            SQLException,
            DataException,
            Exception
     {
		HttpSession session = request.getSession();
		// IISZ updated in 2013-07-11 begin
		//Connection con = this.getConnection();
		// IISZ updated in 2013-07-11 end

        Vector vec = new Vector();

//--------------- 2004-4-22 9:36 Update By Beyes -------------------//
		String dateFrom = "";
		String dateTo = "";
		if(request.getParameter("date_from")!=null && request.getParameter("date_from").length() != 0)
			dateFrom = request.getParameter("date_from");
		if(request.getParameter("date_to")!=null && request.getParameter("date_to").length() != 0)			
			dateTo = request.getParameter("date_to");
		session.setAttribute("s_date_from1",dateFrom);
		session.setAttribute("s_date_to1",dateTo);

		String p_fromYear = "";
		String p_fromMonth = "";
		String p_toYear = "";
		String p_toMonth = "";
		String p_fromDay = "";
		String p_toDay = "";
//		p_fromYear = request.getParameter("year_from");
//		p_fromMonth = request.getParameter("month_from");
//		p_toYear = request.getParameter("year_to");
//		p_toMonth = request.getParameter("month_to");
//		session.setAttribute("year_from",p_fromYear);
//		session.setAttribute("month_from",p_fromMonth);
//		session.setAttribute("year_to",p_toYear);
//		session.setAttribute("month_to",p_toMonth);
		if(dateFrom.length() != 0)
		{
			p_fromYear = dateFrom.substring(0,4);
			p_fromMonth = dateFrom.substring(5,7);
			p_fromDay = dateFrom.substring(8,10);
		}
		if(dateTo.length() != 0)
		{
			p_toYear = dateTo.substring(0,4);
			p_toMonth = dateTo.substring(5,7);		
			p_toDay = dateTo.substring(8,10);
		}
//-------------------------------------------------------------------//

		String companyCodeStr = "";
		if (request.getParameter("company_code") != null)
		{
			companyCodeStr = request.getParameter("company_code");
			session.setAttribute("company",companyCodeStr);
		}
		String groupStr = "";
		if (request.getParameter("cOrg_name") != null)
		{
			groupStr = request.getParameter("cOrg_name");
			session.setAttribute("group",groupStr);
		}
                
/*logger.debug("company Code="+companyCodeStr);
logger.debug("cOrg_name="+groupStr);
logger.debug("year_from="+p_fromYear);
logger.debug("month_from="+p_fromMonth);
logger.debug("year_to="+p_toYear);
logger.debug("month_to="+p_toMonth);                */

		String chargesql = "Select a.grade grade,c.project_group_name group_name,sum(a.spend) hours ";
		chargesql += "From tss_v_project_report a,tss.tss_c_project_category b,tss.tss_c_project c ";
		chargesql += "Where a.project_category_name = b.project_category_name ";
		chargesql += "And a.project_name = c.project_name ";
		chargesql += "And b.company_code = '"+companyCodeStr+"' ";
		if(groupStr != null && groupStr.length() != 0)
		{
			chargesql += "And c.project_group_name = '"+groupStr+"' ";
		}
//--------------- 2004-4-22 9:01 Update By Beyes -------------------//		
		if(p_fromYear != null && p_fromYear.length() != 0 && p_fromMonth != null && p_fromMonth.length() != 0)
		{
			chargesql += "And to_char(a.spend_date,'YYYY/MM/DD') >= '"+p_fromYear+"/"+p_fromMonth+"/"+p_fromDay+"' ";
		}
		if(p_toYear != null && p_toYear.length() != 0 && p_toMonth != null && p_toMonth.length() != 0)
		{
			chargesql += "And to_char(a.spend_date,'YYYY/MM/DD') <= '"+p_toYear+"/"+p_toMonth+"/"+p_toDay+"' ";
		}
//-------------------------------------------------------------------//	
		chargesql += "And c.chargeable_ind = 'N' ";
		chargesql += "Group By a.grade,c.project_group_name ";
		chargesql += "Order By a.grade,c.project_group_name";
		
//logger.debug("SQL is " + chargesql);
		//IISZ updated in 2013-07-11 begin
		Connection con = null;
		PreparedStatement chargestmt =null;
		//IISZ updated in 2013-07-11 end
		ResultSet chargers = null;
		ChargeReportContainer list = new ChargeReportContainer();
			
		try
		{
			// IISZ added in 2013-07-11 begin
			con = this.getConnection();
			chargestmt = con.prepareStatement(chargesql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			// IISZ added in 2013-07-11 end
			HashMap lHs_gradeTotal = new HashMap();
			HashMap lHs_groupTotal = new HashMap();
			HashMap lHs_gradeGroupTotal = new HashMap();
			float lf_total = 0f;
			HashMap lHm_Rate = new HashMap();
			ArrayList lAl_gradeNameList = new ArrayList();
			ArrayList lAl_groupNameList = new ArrayList();

			chargers = chargestmt.executeQuery();
			while (chargers.next())
			{
				float lf_hours = 0f;
				String ls_grade = chargers.getString(1);
				String ls_group = chargers.getString(2);
				lf_hours = chargers.getFloat(3);
				if (!lAl_gradeNameList.contains(ls_grade))
					lAl_gradeNameList.add(ls_grade);
				if (!lAl_groupNameList.contains(ls_group))
					lAl_groupNameList.add(ls_group);
					              
				RateBean lRb_RateBean = RateProcess.createBean();
				lRb_RateBean.setGrade(ls_grade);
				RateBean[] lRb_RateBeanArr = RateProcess.find(con,lRb_RateBean);
																
								//Find a record
				if (lRb_RateBeanArr != null && lRb_RateBeanArr.length>0)
				{
					RateBean lRb_Bean = lRb_RateBeanArr[0];
					if (!lHm_Rate.containsKey(ls_grade))
					{
						lHm_Rate.put(ls_grade, new Float(lRb_Bean.getRate()));
					}
				}
				if(lHs_gradeTotal.containsKey(ls_grade))
				{
					Float gradehours = (Float)lHs_gradeTotal.get(ls_grade);
					Float gh = new Float(gradehours.floatValue()+lf_hours);
					gh = (Float)lHs_gradeTotal.put(ls_grade,gh);
				}
				else
				{
					Float gh = new Float(lf_hours);
					gh = (Float)lHs_gradeTotal.put(ls_grade,gh);
				}
				if (lHs_groupTotal.containsKey(ls_group))
				{
					Float categoryhours = (Float)lHs_groupTotal.get(ls_group);
					Float ch = new Float(categoryhours.floatValue()+lf_hours);
					ch = (Float)lHs_groupTotal.put(ls_group,ch);
				}
				else
				{
					Float ch = new Float(lf_hours);
					ch = (Float)lHs_groupTotal.put(ls_group,ch);
				}

				lf_total += lf_hours;
				Float gch = new Float(lf_hours);
				gch = (Float)lHs_gradeGroupTotal.put(ls_grade + "~" + ls_group,gch);
			}
				
//logger.debug("---------------------- "+count+" --------------------------");
//logger.debug("Group Total:"+lHs_groupTotal);
//logger.debug("Grade Group Total:"+lHs_gradeGroupTotal);
//logger.debug("Total:"+lf_total);
//logger.debug("Rate:"+lHm_Rate);
//logger.debug("Grade Name List:"+lAl_gradeNameList);
//logger.debug("Group Name List:"+lAl_groupNameList);
//logger.debug("---------------------- "+count+" --------------------------");
				
			if (!lHs_gradeTotal.isEmpty())
			{
				list.setGradeTotal(lHs_gradeTotal);
				list.setCategoryTotal(lHs_groupTotal);
				list.setGradeCategoryTotal(lHs_gradeGroupTotal);
				list.setTotal(lf_total);
				list.setRate(lHm_Rate);
				list.setGradeNameList(lAl_gradeNameList);
				list.setCategoryNameList(lAl_groupNameList);
                                                
				vec.addElement(list);
			}				
		}
		catch (Exception e)
		{
			logger.error(e, e);
		}finally{
			//IISZ updated in 2013-07-11 begin
			JdbcUtil.release(chargestmt, chargers);
			//IISZ updated in 2013-07-11 end
		}
		if (vec.isEmpty())
		{
			message.setMessage("No record, please input another criteria.");
			return "tss/projectreport/ProjectReport.jsp";
		}
		else
		{
			request.setAttribute("vec",vec);
			return "tss/projectreport/GSIReport.jsp";
		}
	}
}
